﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class EmpolyDAL
    {
        public List<EmployInfo> GetEmpoly(string empName, int begin, int end, out int total)
        {
            string sql = "";
            string strWhere = " where 1=1";
            string abc = string.Format(" where 1=1 and EmpolyName like '%{0}%'", empName);
            if (empName == string.Empty)
            {
                sql = string.Format(@"select * from (select ROW_NUMBER()over(order by EmpolyID desc) rowIndex, a.*,b.DeptName,c.RoleName from EmpolyInfo a 
            left join DeptInfo b on a.DeptID = b.DeptID
            left join RoleInfo c on a.RoleID = c.RoleID) d
              where rowindex between {0} and {1}", begin, end);
            }
            else
            {
                sql = string.Format(@"select * from (select ROW_NUMBER()over(order by EmpolyID desc) rowIndex, a.*,b.DeptName,c.RoleName from EmpolyInfo a 
            left join DeptInfo b on a.DeptID = b.DeptID
            left join RoleInfo c on a.RoleID = c.RoleID) d {0}", abc);
            }
            DataTable dt = DBHelper.GetTable(sql);
            List<EmployInfo> list = new List<EmployInfo>();
            if (dt.Rows.Count>0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    EmployInfo info = new EmployInfo();
                    info.EmpolyID = Convert.ToInt32(dt.Rows[i]["EmpolyID"]);
                    info.EmpolyNum =dt.Rows[i]["EmpolyNum"].ToString();
                    info.EmpolyName = dt.Rows[i]["EmpolyName"].ToString();
                    info.EmpolySex = dt.Rows[i]["EmpolySex"].ToString();
                    info.EmpolyIdCard = dt.Rows[i]["EmpolyIdCard"].ToString();
                    info.DeptName = dt.Rows[i]["DeptName"].ToString();
                    info.EmpolyLevel = dt.Rows[i]["EmpolyLevel"].ToString();
                    info.RoleName = dt.Rows[i]["RoleName"].ToString();
                    info.InductionDate = dt.Rows[i]["InductionDate"].ToString();
                    info.DepartureDate = dt.Rows[i]["DepartureDate"].ToString();
                    info.EmpolyReMark = dt.Rows[i]["EmpolyReMark"].ToString();
                    list.Add(info);
                }
            }
            string sqlcount ="select COUNT(*) from EmpolyInfo "+strWhere;
            DataTable dtcount = DBHelper.GetTable(sqlcount);
            total = Convert.ToInt32(dtcount.Rows[0][0]);
            return list;
        }

        public List<EmployInfo> BindList(string dept)
        {
            string sql = string.Format(@"select * from (select ROW_NUMBER() over (order by EmpolyID)
rowindex,a.*,b.DeptName,c.RoleName from EmpolyInfo a
join DeptInfo b on a.DeptID =b.DeptID
join RoleInfo c on a.RoleID=c.RoleID) d") ;
            DataTable dt = DBHelper.GetTable(sql);
            List<EmployInfo> list = new List<EmployInfo>();
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    EmployInfo info = new EmployInfo();
                    info.DeptID = Convert.ToInt32(dt.Rows[i]["DeptID"]);
                    info.DeptName = dt.Rows[i]["DeptName"].ToString();
                    info.RoleID = Convert.ToInt32(dt.Rows[i]["RoleID"]);
                    info.RoleName = dt.Rows[i]["RoleName"].ToString();
                    list.Add(info);
                }
            }
            return list;
        }
    }
}
